package service

import (
	"time"

	"agentdesign/models"
)

// ListProjectPages returns all pages for a project ordered by sort order.
func (s *ApiService) ListProjectPages(projectID string) ([]models.ProjectPage, error) {
	pages := []models.ProjectPage{}
	err := s.db.Select(&pages, `
		SELECT id, project_id, slug, name, summary, status, device, screen_count, thumbnail, sort_order, current_version_id, updated_at
		FROM project_pages
		WHERE project_id = ?
		ORDER BY sort_order ASC, updated_at DESC`, projectID)

	return pages, err
}

// GetProjectPage returns a single page for a project.
func (s *ApiService) GetProjectPage(projectID, pageID string) (models.ProjectPage, error) {
	page := models.ProjectPage{}
	err := s.db.Get(&page, `
		SELECT id, project_id, slug, name, summary, status, device, screen_count, thumbnail, sort_order, current_version_id, updated_at
		FROM project_pages
		WHERE project_id = ? AND id = ?`, projectID, pageID)

	return page, err
}

// CreateProjectPage inserts a new project page record along with its initial version.
func (s *ApiService) CreateProjectPage(page models.ProjectPage) (models.ProjectPage, error) {
	tx, err := s.db.Beginx()
	if err != nil {
		return models.ProjectPage{}, err
	}

	rollback := func(e error) (models.ProjectPage, error) {
		_ = tx.Rollback()
		return models.ProjectPage{}, e
	}

	generateID := func() (string, error) {
		var id string
		if err := tx.Get(&id, "SELECT UUID()"); err != nil {
			return "", err
		}
		return id, nil
	}

	pageID := page.ID
	if pageID == "" {
		if pageID, err = generateID(); err != nil {
			return rollback(err)
		}
	}

	if page.CurrentVersionID == "" {
		if page.CurrentVersionID, err = generateID(); err != nil {
			return rollback(err)
		}
	}

	_, err = tx.Exec(`
		INSERT INTO project_pages
			(id, project_id, slug, name, summary, device, screen_count, thumbnail, current_version_id)
		VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)`,
		pageID,
		page.ProjectID,
		page.Slug,
		page.Name,
		page.Summary,
		page.Device,
		page.ScreenCount,
		page.Thumbnail,
		page.CurrentVersionID,
	)
	if err != nil {
		return rollback(err)
	}

	_, err = tx.Exec(`
		INSERT INTO page_versions (id, page_id, version_number, is_active, created_by)
		VALUES (?, ?, ?, 1, NULL)`,
		page.CurrentVersionID,
		pageID,
		1,
	)
	if err != nil {
		return rollback(err)
	}

	if err := tx.Commit(); err != nil {
		return models.ProjectPage{}, err
	}

	return s.GetProjectPage(page.ProjectID, pageID)
}

// UpdateProjectPage updates editable fields for a page.
func (s *ApiService) UpdateProjectPage(page models.ProjectPage) (int64, error) {
	currentVersionID := any(nil)
	if page.CurrentVersionID != "" {
		currentVersionID = page.CurrentVersionID
	}

	result, err := s.db.Exec(`
		UPDATE project_pages
		SET
			name = ?,
			summary = ?,
			device = ?,
			screen_count = ?,
			thumbnail = ?,
			current_version_id = ?,
			updated_at = ?
		WHERE id = ? AND project_id = ?`,
		page.Name,
		page.Summary,
		page.Device,
		page.ScreenCount,
		page.Thumbnail,
		currentVersionID,
		time.Now(),
		page.ID,
		page.ProjectID,
	)
	if err != nil {
		return 0, err
	}

	return result.RowsAffected()
}

// DeleteProjectPage removes a page by identifier.
func (s *ApiService) DeleteProjectPage(projectID, pageID string) (int64, error) {
	result, err := s.db.Exec("DELETE FROM project_pages WHERE project_id = ? AND id = ?", projectID, pageID)
	if err != nil {
		return 0, err
	}

	return result.RowsAffected()
}
